﻿using System;
using Model;
using System.Data.SQLite;
using System.Collections.Generic;

//数据访问层
namespace DAL
{
    public class StudentRepository
    {
        private string connectionString = "Data Source=test.db;Version=3;";

        public void AddStudent(Student student)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Student (StudentName, Age, ClassID) VALUES (@name, @age, @classId)";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", student.StudentName);
                    cmd.Parameters.AddWithValue("@age", student.Age);
                    cmd.Parameters.AddWithValue("@classId", student.ClassID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void DeleteStudent(int studentId)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "DELETE FROM Student WHERE StudentID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@id", studentId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void UpdateStudent(Student student)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE Student SET StudentName = @name, Age = @age, ClassID = @classId WHERE StudentID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", student.StudentName);
                    cmd.Parameters.AddWithValue("@age", student.Age);
                    cmd.Parameters.AddWithValue("@classId", student.ClassID);
                    cmd.Parameters.AddWithValue("@id", student.StudentID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

          // 根据学生姓名进行模糊查询
        public List<Student> SearchStudentsByName(string name)
        {
            var students = new List<Student>();
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Class";
                if (name != "") {
                    query = "SELECT * FROM Student WHERE StudentName LIKE @name";
                }
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + name + "%");
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var student = new Student
                            {
                                StudentID = Convert.ToInt32(reader["StudentID"]),
                                StudentName = reader["StudentName"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                ClassID = Convert.ToInt32(reader["ClassID"])
                            };
                            students.Add(student);
                        }
                    }
                }
            }
            return students;
        }
    }
}
